#json !/usr/bin/env python
#-*- coding:utf-8 -*-

import os
import sys
import datetime
reload(sys) 
sys.setdefaultencoding('utf8')
import time
import MySQLdb
sys.path.insert(0,'/home/liuyue/svn/wan/')
sys.path.insert(0,'/opt/web/')
sys.path.insert(0,'/opt/wanalytics/')
from Tools import connectMySQL




def TotalArea():
    _yesday = datetime.datetime.today() + datetime.timedelta(-1)
    _datetime = _yesday.strftime('%Y%m%d')
    _datesql = " and date = '%s' " % _datetime
    #_datesql = " "
    #数据库连接
    try:
        db = connectMySQL.connMySQL("stat")
    except MySQLdb.Error, e:
        resultStr = '{"status": "failed", "code": "4001", "str": "connect database failed"}'
        return resultStr
    db.autocommit(True)
    c = db.cursor()

    
    
    
    os_array = ['-1','61','59','52']
    device_array = ['-1','1','2']
    for os in os_array:
        for device in device_array:
            
            if os == '-1':
                _os = '61,59,52'
            else:
                _os = os
            if device == '-1':
                _device = '1,2'
            else:
                _device = device
                
                
            sql = " SELECT sum(a.uv),coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),date FROM `stat_day_total` a left join `stat_city_dict` b on a.prov = b.cprov_id and a.city = b.ccity_id WHERE  a.business_type = 2 and a.pid_stat = 1 and a.os_id in (%s) and a.device_type in (%s) and a.partner_mode in (1,2,3,8) and a.prov != 'ALL' and a.city != 'ALL' and a.ver = 'ALL' %s group by coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),a.date " % (_os,_device,_datesql)
            #print sql
            c.execute(sql)  
            result = c.fetchall()
            
            for x in result:
                sql = " insert into `result_total_area` (`uv`,`prov`,`city`,`date`,`os_id`,`device_type`) values ('%s','%s','%s','%s','%s','%s') " % (x[0],x[1],x[2],x[3],os,device)
                #print sql
                try:
                    c.execute(sql)
                except Exception,e:
                    c.execute("update result_total_area set uv = '%s' where prov = '%s' and city = '%s' and date = %s and os_id = '%s' and device_type = '%s' " % (x[0],x[1],x[2],x[3],os,device) )
                    print e
    
      
    
    c.close()
    db.close()
    
    
def PaikeArea():
    _yesday = datetime.datetime.today() + datetime.timedelta(-1)
    _datetime = _yesday.strftime('%Y%m%d')
    _datesql = " and date = '%s' " % _datetime
    #_datesql = " "
    #数据库连接
    try:
        db = connectMySQL.connMySQL("stat")
    except MySQLdb.Error, e:
        resultStr = '{"status": "failed", "code": "4001", "str": "connect database failed"}'
        return resultStr
    db.autocommit(True)
    c = db.cursor()

    
    
    
    os_array = ['-1','61','52']
    device_array = ['-1','1','2']
    for os in os_array:
        for device in device_array:
            
            if os == '-1':
                _os = '61,52'
            else:
                _os = os
            if device == '-1':
                _device = '1,2'
            else:
                _device = device
                
                
            sql = " SELECT sum(a.uv),coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),date FROM `stat_day_paike` a left join `stat_city_dict` b on a.prov = b.cprov_id and a.city = b.ccity_id WHERE  a.business_type = 4 and a.pid_stat = 1 and a.os_id in (%s) and a.device_type in (%s) and a.partner_mode in (1,2,3,8) and a.prov != 'ALL' and a.city != 'ALL' and a.ver = 'ALL' %s group by coalesce(b.prov_cn,'未知'),coalesce(b.city_cn,'未知'),a.date " % (_os,_device,_datesql)
            #print sql
            c.execute(sql)  
            result = c.fetchall()
            
            for x in result:
                sql = " insert into `result_paike_area` (`uv`,`prov`,`city`,`date`,`os_id`,`device_type`) values ('%s','%s','%s','%s','%s','%s') " % (x[0],x[1],x[2],x[3],os,device)
                try:
                    c.execute(sql)
                except Exception,e:
                    c.execute("update result_paike_area set uv = '%s' where prov = '%s' and city = '%s' and date = %s and os_id = '%s' and device_type = '%s' " % (x[0],x[1],x[2],x[3],os,device) )
                    print e
    
      
    
    c.close()
    db.close()
    





if __name__ == '__main__':
    cmd = ''
    if len(sys.argv) == 2:
        cmd = sys.argv[1]

    if cmd == 'totalarea':
        print "TotalArea()"
        TotalArea()
    elif cmd == 'paikearea':
        print "PaikeArea()"
        PaikeArea()
    else:
        print "Nothing Happen!"
